﻿using System;
using System.Data;
using System.Data.SqlClient;

using ns_User;

namespace ns_DatabaseOperation//数据库操作
{
    public interface i_DBA
    {
        c_User GetUser(String name);
        c_User GetUser(Int32 ID);
        Boolean UpdateUser(c_User user);
        Boolean AddUser(c_User user);
        Boolean DeleteUser(String name);
        Boolean DeleteUser(Int32 ID);
        Boolean IsUserExist(String name);
        Boolean IsUserExist(Int32 ID);
        Int32 NewID();
        Int32 GetAllUsers();
    }
    public class c_SqlDatabaseOperation  : i_DBA//用户资料读写
    {
        private const String CONNECTIONSTRING = "server=(local);database=db_iChat;uid=SAYKIA;pwd=200630471250;integrated security=true;";
        
        
        public c_User GetUser(String name)
        {
            SqlConnection con = new SqlConnection(CONNECTIONSTRING);
            c_User user = null;
            try 
            {
                SqlCommand cmd = new SqlCommand("pro_selectUsersByName",con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@name", name);
                con.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    user=new c_User();
                    user.ID = dr.GetInt32(0);
                    user.Name = dr.GetString(1);
                    user.HashedPassword = dr.GetSqlBytes(2).Value;
                    user.Question = dr.GetString(3);
                    user.HashedAnswer = dr.GetSqlBytes(4).Value;
                    user.Email = dr.GetString(5);
                    user.SighWords = dr.GetString(6);
                    user.Sex = dr.GetString(7);
                    user.MsgNotSentXmlPath = dr.GetString(8);
                    user.MsgGotRecordsXmlPath = dr.GetString(9);
                    user.MsgSentRecordsXmlPath = dr.GetString(10);
                    user.FriendsListXmlPath = dr.GetString(11);
                    user.Address = dr.GetString(12);
                    user.ImgHeadUrl = dr.GetString(13);
                    user.ImgHeadPath = dr.GetString(14);
                    user.RegTime = dr.GetDateTime(15);
                    user.LastLogTime = dr.GetDateTime(16);
                    user.LogTimes = dr.GetInt32(17);
                    user.Points = dr.GetInt32(18);
                    user.Roles = dr.GetInt32(19);
                    user.Phone = dr.GetString(20);         
                }
                con.Dispose();
                return user;

            }
            catch (Exception e)
            {
                con.Dispose();
                return null;
            }
        }

        public c_User GetUser(Int32 ID)
        {
            SqlConnection con = new SqlConnection(CONNECTIONSTRING);
            c_User user = null;
            try
            {
                SqlCommand cmd = new SqlCommand("pro_selectUsersByID", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ID", ID);
                con.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    user = new c_User();
                    user.ID = dr.GetInt32(0);
                    user.Name = dr.GetString(1);
                    user.HashedPassword = dr.GetSqlBytes(2).Value;
                    user.Question = dr.GetString(3);
                    user.HashedAnswer = dr.GetSqlBytes(4).Value;
                    user.Email = dr.GetString(5);
                    user.SighWords = dr.GetString(6);
                    user.Sex = dr.GetString(7);
                    user.MsgNotSentXmlPath = dr.GetString(8);
                    user.MsgGotRecordsXmlPath = dr.GetString(9);
                    user.MsgSentRecordsXmlPath = dr.GetString(10);
                    user.FriendsListXmlPath = dr.GetString(11);
                    user.Address = dr.GetString(12);
                    user.ImgHeadUrl = dr.GetString(13);
                    user.ImgHeadPath = dr.GetString(14);
                    user.RegTime = dr.GetDateTime(15);
                    user.LastLogTime = dr.GetDateTime(16);
                    user.LogTimes = dr.GetInt32(17);
                    user.Points = dr.GetInt32(18);
                    user.Roles = dr.GetInt32(19);
                    user.Phone = dr.GetString(20);
                }
                con.Dispose();
                return user;

            }
            catch (Exception e)
            {
                con.Dispose();
                return null;
            }
        }



        public Boolean AddUser(c_User user)
        {
            SqlConnection con = new SqlConnection(CONNECTIONSTRING);
            try
            {
                SqlCommand cmd = new SqlCommand("pro_insertUsers", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@name",user.Name);
                cmd.Parameters.AddWithValue("@hashedPassword",user.HashedPassword);
                cmd.Parameters.AddWithValue("@phone",user.Phone);
                cmd.Parameters.AddWithValue("@roles",user.Roles);
                cmd.Parameters.AddWithValue("@points",user.Points);
                cmd.Parameters.AddWithValue("@logTimes",user.LogTimes);
                cmd.Parameters.AddWithValue("@lastLogTime",user.LastLogTime);
                cmd.Parameters.AddWithValue("@regTime",user.RegTime);
                cmd.Parameters.AddWithValue("@imgHeadUrl",user.ImgHeadUrl);
                cmd.Parameters.AddWithValue("@imgHeadPath",user.ImgHeadPath);
                cmd.Parameters.AddWithValue("@friendsListXmlPath",user.FriendsListXmlPath);
                cmd.Parameters.AddWithValue("@address",user.Address);
                cmd.Parameters.AddWithValue("@msgSentRecordsXmlPath",user.MsgSentRecordsXmlPath);
                cmd.Parameters.AddWithValue("@msgGotRecordsXmlPath",user.MsgGotRecordsXmlPath);
                cmd.Parameters.AddWithValue("@msgNotSentXmlPath",user.MsgNotSentXmlPath);
                cmd.Parameters.AddWithValue("@sighWords",user.SighWords);
                cmd.Parameters.AddWithValue("@question",user.Question );
                cmd.Parameters.AddWithValue("@hashedAnswer",user.HashedAnswer);
                cmd.Parameters.AddWithValue("@email",user.Email);
                cmd.Parameters.AddWithValue("@sex",user.Sex);
                cmd.Parameters.AddWithValue("@ID",user.ID); 
                con.Open();
                if (cmd.ExecuteNonQuery() <= 0)
                {
                    con.Dispose();
                    return false;
                }
                con.Dispose();
                return true;

            }
            catch (Exception e)
            {
                con.Dispose();
                return false;
            }
        }

        public Boolean UpdateUser(c_User user)
        {
            SqlConnection con = new SqlConnection(CONNECTIONSTRING);
            try
            {
                SqlCommand cmd = new SqlCommand("pro_updateUsers", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@name", user.Name);
                cmd.Parameters.AddWithValue("@hashedPassword", user.HashedPassword);
                cmd.Parameters.AddWithValue("@phone", user.Phone);
                cmd.Parameters.AddWithValue("@roles", user.Roles);
                cmd.Parameters.AddWithValue("@points", user.Points);
                cmd.Parameters.AddWithValue("@logTimes", user.LogTimes);
                cmd.Parameters.AddWithValue("@lastLogTime", user.LastLogTime);
                cmd.Parameters.AddWithValue("@regTime", user.RegTime);
                cmd.Parameters.AddWithValue("@imgHeadUrl", user.ImgHeadUrl);
                cmd.Parameters.AddWithValue("@imgHeadPath", user.ImgHeadPath);
                cmd.Parameters.AddWithValue("@friendsListXmlPath", user.FriendsListXmlPath);
                cmd.Parameters.AddWithValue("@address", user.Address);
                cmd.Parameters.AddWithValue("@msgSentRecordsXmlPath", user.MsgSentRecordsXmlPath);
                cmd.Parameters.AddWithValue("@msgGotRecordsXmlPath", user.MsgGotRecordsXmlPath);
                cmd.Parameters.AddWithValue("@msgNotSentXmlPath", user.MsgNotSentXmlPath);
                cmd.Parameters.AddWithValue("@sighWords", user.SighWords);
                cmd.Parameters.AddWithValue("@question", user.Question);
                cmd.Parameters.AddWithValue("@hashedAnswer", user.HashedAnswer);
                cmd.Parameters.AddWithValue("@email", user.Email);
                cmd.Parameters.AddWithValue("@sex", user.Sex);
                cmd.Parameters.AddWithValue("@ID", user.ID);
                con.Open();
                if (cmd.ExecuteNonQuery() == 0)
                {
                    con.Dispose();
                    return false;
                }
                con.Dispose();
                return true;

            }
            catch (Exception e)
            {
                con.Dispose();
                return false;
            }
        }

        public Boolean DeleteUser(String name)
        {
            SqlConnection con = new SqlConnection(CONNECTIONSTRING);
            try
            {
                SqlCommand cmd = new SqlCommand("pro_deleteUsersByName", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@name", name);
                con.Open();
                if (cmd.ExecuteNonQuery() <= 0)
                {
                    con.Dispose();
                    return false;
                }
                con.Dispose();
                return true;

            }
            catch (Exception e)
            {
                con.Dispose();
                return false;
            }
        }

        public Boolean DeleteUser(Int32 ID)
        {
            SqlConnection con = new SqlConnection(CONNECTIONSTRING);
            try
            {
                SqlCommand cmd = new SqlCommand("pro_deleteUsersByID", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ID", ID);
                con.Open();
                if (cmd.ExecuteNonQuery() <= 0)
                {
                    con.Dispose();
                    return false;
                }
                con.Dispose();
                return true;

            }
            catch (Exception e)
            {
                con.Dispose();
                return false;
            }
        }

        public Boolean IsUserExist(String name)
        {
            SqlConnection con = new SqlConnection(CONNECTIONSTRING);
            try
            {
                SqlCommand cmd = new SqlCommand("pro_selectUsersByName", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@name", name);
                con.Open();
                if (cmd.ExecuteScalar()==null)
                {
                    con.Dispose();
                    return false;
                }
                else
                {
                    con.Dispose();
                    return true;
                }
            }
            catch (Exception e)
            {
                con.Dispose();
                return true;//操作异常，默认存在
            }
        }

        public Boolean IsUserExist(Int32 ID)
        {
            SqlConnection con = new SqlConnection(CONNECTIONSTRING);
            try
            {
                SqlCommand cmd = new SqlCommand("pro_selectUsersByID", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ID", ID);
                con.Open();
                if (cmd.ExecuteScalar()==null)
                {
                    con.Dispose();
                    return false;
                }
                else
                {
                    con.Dispose();
                    return true;
                }

            }
            catch (Exception e)
            {
                con.Dispose();
                return true;//操作异常，默认存在
            }
        }
        public Int32 GetAllUsers()
        {
            SqlConnection con = new SqlConnection(CONNECTIONSTRING);
            try
            {
                SqlCommand cmd = new SqlCommand("pro_selectUsersNumber", con);
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                Int32 n = (Int32)cmd.ExecuteScalar();
                con.Dispose();
                return n;

            }
            catch (Exception e)
            {
                con.Dispose();
                return 0;
            }
        }
        public Int32 NewID()
        {
            Int32 i = 1;
            for (; IsUserExist(i); i++) { }
            return i;
        }
    }
}